package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import br.com.fiap.trabalho.entity.Movie;
import br.com.fiap.trabalho.entity.Studio;

public class JDBCStudioDAO implements StudioDAO {
	
	private Map<Integer, Studio> studiosMap = new HashMap<Integer, Studio>();
	
	public Studio createStudio(Studio studio) {
		
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			connection.setAutoCommit(false);
			
			if (studio.getId() == null) {
				insertStudio(studio, connection);
			} else {
				updateStudio(studio, connection);
			}
			
			connection.commit();
			connection.setAutoCommit(true);
			
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			studio = null;
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return studio;
	}

	public List<Studio> selectStudioByName(String name) {
		
		List<Studio> studios = new ArrayList<Studio>();
		
		try {
			Connection connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select s.id as studio_id, s.name as studio_name, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from studio s left join movie m on s.id = m.studio_id ");
			sql.append(" where s.name like ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+name+"%");

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				studios.add(getResutSetStudio(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return studios;
	}
	
	private void setIdOnEntity(Studio studio, PreparedStatement ps) throws SQLException {
		
		ResultSet generatedKeys = ps.getGeneratedKeys();
		if (generatedKeys.next()) {
		    studio.setId(generatedKeys.getInt(1));
		}
		
	}
	
	private void insertStudio(Studio studio, Connection connection)
			throws SQLException {
		String sql = "insert into studio (name) values (?)";
		
		PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, studio.getName());
		
		ps.executeUpdate();
		
		setIdOnEntity(studio, ps);
		
		if (studio.getMovies() != null && studio.getMovies().isEmpty()) {
			List<Movie> persistedMovie = new LinkedList<Movie>();
			
			for (Movie movie : studio.getMovies()) {
				String sqlMovie = "insert into movie (title, year_movie, studio_id) values (?, ?, ?)";
				
				PreparedStatement psMovie = connection.prepareStatement(sqlMovie, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, movie.getTitle());
				ps.setInt(2, movie.getYear());
				ps.setInt(3, movie.getStudio().getId());
				
				ps.executeUpdate();
				
				ResultSet generatedKeys = psMovie.getGeneratedKeys();
				if (generatedKeys.next()) {
					movie.setId(generatedKeys.getInt(1));
				}
				
				ps.close();
				
				persistedMovie.add(movie);
				
			}
			
			studio.setMovies(persistedMovie);
		}
		
		ps.close();
	}
	
	private void updateStudio(Studio studio, Connection connection)
			throws Exception {
		
		String selectById = "select * from studio where id = ?";
		
		PreparedStatement ps = connection.prepareStatement(selectById);
		ps.setInt(1, studio.getId());
		
		ResultSet rs = ps.executeQuery();
		
		Studio persistedStudio = null;
		
		if (rs.next()) {
			persistedStudio = new Studio();
				
			persistedStudio.setId(rs.getInt("id"));
			persistedStudio.setName(rs.getString("name"));
		}
			
		if (persistedStudio == null) {
			throw new Exception("No Studio found with id = " + studio.getId());
		}
			
		String sql = "update studio set name = ?";
		
		ps = connection.prepareStatement(sql);
		ps.setString(1, studio.getName());
		
		ps.executeUpdate();
		
		ps.close();
	}
	
	private Studio getResutSetStudio(ResultSet rs) throws SQLException {
		Studio cachedStudio = studiosMap.get(rs.getInt("studio_id"));
		
		if (cachedStudio == null) {
			cachedStudio = new Studio();
			
			cachedStudio.setId(rs.getInt("studio_id"));
			cachedStudio.setName(rs.getString("studio_name"));
			
			try{
				rs.getInt("movie_id");
			}catch(SQLException sqe){
				return cachedStudio;
			}
			
			studiosMap.put(cachedStudio.getId(), cachedStudio);
		}
		
		List<Movie> movies = cachedStudio.getMovies();
		
		Integer idMovie = rs.getInt("movie_id");
		
		if (idMovie != 0 && movies == null) {
			movies = new ArrayList<Movie>();
			cachedStudio.setMovies(movies);
			
			Movie movie = new Movie();
			movie.setId(rs.getInt("movie_id"));
			movie.setTitle(rs.getString("movie_title"));
			movie.setYear(rs.getInt("movie_year"));
			
			movies.add(movie);
		}
		
		return cachedStudio;
	}


	public List<Studio> selectStudios(int numberOfElements) {
		
		List<Studio> studios = new ArrayList<Studio>();
		
		try {
			Connection connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select s.id as studio_id, s.name as studio_name");
			sql.append(" from studio s ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
		
			ps.setMaxRows(numberOfElements);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				studios.add(getResutSetStudio(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return studios;
	}
}
